﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Public;

namespace DAL
{
    public class TuoiDAL
    {
        public static List<TuoiPulic> LoadBieuDoTuoi()
        {
            string chuoi = @"
                            select cast(1 as tinyint) as MaT,'<13' as MucTuoi,(select COUNT(MaNK)) as SL from Nhankhau where DATEDIFF(YYYY,NgaySinh,getdate())<13
                            union all 
                            select cast(2 as tinyint) as MaT,'14 - 17' as MucTuoi,(select COUNT(MaNK)) as SL from Nhankhau where DATEDIFF(YYYY,NgaySinh,getdate()) between 14 and 17
                            union all 
                            select cast(3 as tinyint) as MaT,'18 - 24' as MucTuoi,(select COUNT(MaNK)) as SL from Nhankhau where DATEDIFF(YYYY,NgaySinh,getdate()) between 18 and 24
                            union all 
                            select cast(4 as tinyint) as MaT,'25 - 34' as MucTuoi,(select COUNT(MaNK)) as SL from Nhankhau where DATEDIFF(YYYY,NgaySinh,getdate()) between 25 and 34
                            union all 
                            select cast(5 as tinyint) as MaT,'35 - 44' as MucTuoi,(select COUNT(MaNK)) as SL from Nhankhau where DATEDIFF(YYYY,NgaySinh,getdate()) between 35 and 44
                            union all 
                            select cast(6 as tinyint) as MaT,'45 - 64' as MucTuoi,(select COUNT(MaNK)) as SL from Nhankhau where DATEDIFF(YYYY,NgaySinh,getdate()) between 45 and 64
                            union all 
                            select cast(7 as tinyint) as MaT,'>=65' as MucTuoi,(select COUNT(MaNK)) as SL from Nhankhau where DATEDIFF(YYYY,NgaySinh,getdate()) >65
                           ";
            var tuoi = Common.db.ExecuteStoreQuery<TuoiPulic>(chuoi);
            return tuoi.ToList();
        }
    }
}
